package com.sstock.smartstock;

import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


import net.sourceforge.pinyin4j.PinyinHelper;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

public class StockListDB {
	
	public static final String STOCKLISTDB_NAME = "stocklist.db";
	public static final String STOCKLISTDB_TABLE = "stocklist";
	private static final int STOCKLISTDB_VERSION = 1;
	
	public static final String KEY_ID = "_id";
	public static final String KEY_STOCK_SEARCH = "stock_code_search";
	public static final String KEY_STOCK_NAME= "stock_name";
	public static final String KEY_STOCK_CODE = "stock_code";
	public static final String KEY_STOCK_PINYIN= "stock_pinyin";
	
	
	SQLiteDatabase stocklistdb = null;
	private Context context;
	private toDoDBOpenHelper dbHelper;
	private StockSearch tempStockSearch;
	
	
	public StockListDB(Context context) {
		this.context = context;
		dbHelper = new toDoDBOpenHelper(context, STOCKLISTDB_NAME,
				null, STOCKLISTDB_VERSION);
	}
	
	public StockListDB(RefreshService refreshService) {
		this.context = refreshService;
		dbHelper = new toDoDBOpenHelper(context, STOCKLISTDB_NAME,
										null, refreshService.STOCKLISTDB_VERSION);
	}
	/*
	 * 
	 */
	/*public StockListDB(Activity activity){
	 * 	this.context = activity;
	 * 	dbHelper = new toDoDBOpenHelper(activity, STOCKLISTDB_NAME,
	 * 			null, STOCKLISTDB_VERSION);
	 * }
	 * */
	
	/**
	 * 
	 */
	public void closeSynchronous() {
		
		stocklistdb.execSQL("PRAGMA synchronous = OFF;");
	}
	/*
	 * Close the database
	 */
	public void close()
	{
		stocklistdb.close();
	}
	
	/*
	 * Open the database
	 */
	public void open() throws SQLiteException {
		try{
			stocklistdb = dbHelper.getWritableDatabase();
		}catch (SQLiteException ex) {
			stocklistdb = dbHelper.getReadableDatabase();
		}
	}
	
	/**
	 * Insert a new stock
	 */
	public long insertStock(StockSearch stock) {
		//Create a new row of values to insert
		ContentValues newStockList = new ContentValues(); 
		
		//Assign values for each row
		newStockList.put(KEY_STOCK_SEARCH, stock.getStockSearch());
		newStockList.put(KEY_STOCK_NAME, stock.getStockName());
		newStockList.put(KEY_STOCK_CODE, stock.getStockCode());
		newStockList.put(KEY_STOCK_PINYIN, stock.getStockPinYin());
		
		//Insert the row
		opened();
		return stocklistdb.insert(STOCKLISTDB_TABLE, null, newStockList);
		
	}
	
	/**
	 * 
	 * @param ArrayList<StockSearch>
	 * @return
	 */
	public void insertStockArray(ArrayList<StockSearch> tempStockArray) {
		
		int i;
		opened();
		for(i = 1; i < tempStockArray.size(); i++)
		{
			tempStockSearch = tempStockArray.get(i);
			ContentValues newStockList = new ContentValues(); 
			
			newStockList.put(KEY_STOCK_SEARCH, tempStockSearch.getStockSearch());
			newStockList.put(KEY_STOCK_NAME, tempStockSearch.getStockName());
			newStockList.put(KEY_STOCK_CODE, tempStockSearch.getStockCode());
			newStockList.put(KEY_STOCK_PINYIN, tempStockSearch.getStockPinYin());
			Log.v("insert is ",tempStockSearch.getStockCode());
			//stocklistdb.insert(STOCKLISTDB_TABLE, null, newStockList);
		}
		stocklistdb.beginTransaction();
		
		try
		{
			for(i = 0; i < tempStockArray.size(); i++)
			{
				tempStockSearch = tempStockArray.get(i);
				ContentValues newStockList = new ContentValues(); 
				
				newStockList.put(KEY_STOCK_SEARCH, tempStockSearch.getStockSearch());
				newStockList.put(KEY_STOCK_NAME, tempStockSearch.getStockName());
				newStockList.put(KEY_STOCK_CODE, tempStockSearch.getStockCode());
				newStockList.put(KEY_STOCK_PINYIN, tempStockSearch.getStockPinYin());
				//Log.v("insert is ",tempStockSearch.getStockCode());
				//stocklistdb.insert(STOCKLISTDB_TABLE, null, newStockList);
			}
			
			stocklistdb.setTransactionSuccessful();
		}
		finally
		{
			stocklistdb.endTransaction();
		}
	}
	
	/*
	 * Remove the stock list by KEY_ID
	 */
	public boolean removebyIndex(long _rowIndex) {
		opened();
		
	    return stocklistdb.delete(STOCKLISTDB_TABLE, KEY_ID + "=" + _rowIndex, null) > 0;
	}
	
	/*
	 * Remove the stock list by Stock Code
	 */
	public boolean removeByStock(String _stock_code) {
		opened();
		
		return stocklistdb.delete(STOCKLISTDB_TABLE, KEY_STOCK_CODE + "=" +"\""+ _stock_code +"\"", null) > 0;
	}
	
	/*
	 * Add for delete quick
	 */
	public Cursor setCursorToKeyStock(String _stock_code) throws SQLException {
		Cursor result = stocklistdb.query(true, STOCKLISTDB_TABLE,
				                 new String[] {KEY_ID, KEY_STOCK_CODE},
				                 KEY_STOCK_CODE + "=" +"\""+ _stock_code +"\"", null, null, null,
				                 null,null);
		if((result.getCount() == 0) || !result.moveToFirst()) {
			//throw new SQLException("No Key_STOCK items find for now: " + _stock_code);
			return result;
		}
		return result;
	}
	
	/*
	 * Get all stock list details 
	 */
	public Cursor getAllStockFormatCursor() {
		opened();
	    return stocklistdb.query(STOCKLISTDB_TABLE, 
	                    new String[] { KEY_ID, KEY_STOCK_SEARCH, KEY_STOCK_NAME, KEY_STOCK_CODE, KEY_STOCK_PINYIN}, 
	                    null, null, null, null, null);
	}
	
	/*
	 * Get what you want from the DB search the stock with 3 methods
	 */
	public Cursor getStockCursor(String _stock) throws SQLException {
		opened();
		Cursor cursor;
		if(isNum(_stock))
		{
			cursor = stocklistdb.query(true, STOCKLISTDB_TABLE, 
	                new String[] {KEY_ID, KEY_STOCK_SEARCH,KEY_STOCK_CODE,KEY_STOCK_NAME,KEY_STOCK_PINYIN},
	                KEY_STOCK_CODE + "=" +"\""+ _stock +"\"", null, null, null, 
	                null, null);
			if ((cursor.getCount() == 0) || !cursor.moveToFirst()) {
				//throw new SQLException("No stock item found for row: " + _stock);
				return null;
		    	}
			return cursor;
		}
		else if(isPinYin(_stock))
		{
			if(isChinese(_stock))
			{
				cursor = stocklistdb.query(true, STOCKLISTDB_TABLE, 
		                new String[] {KEY_ID, KEY_STOCK_SEARCH,KEY_STOCK_CODE,KEY_STOCK_NAME,KEY_STOCK_PINYIN},
		                KEY_STOCK_NAME +" like '"+"%"+"%"+ _stock  +"%"+"%'", null, null, null,
		                null, null);
				if ((cursor.getCount() == 0) || !cursor.moveToFirst()) {
					//throw new SQLException("No stock item found for row: " + _stock);
					return null;
			    	}
				return cursor;
			}
			else
			{
				cursor = stocklistdb.query(true, STOCKLISTDB_TABLE, 
						new String[] {KEY_ID, KEY_STOCK_SEARCH,KEY_STOCK_CODE,KEY_STOCK_NAME,KEY_STOCK_PINYIN},
						KEY_STOCK_PINYIN +" like '"+"%"+"%"+ _stock  +"%"+"%'", null, null, null, 
						null, null);
				if ((cursor.getCount() == 0) || !cursor.moveToFirst()) {
					return null;
					//throw new SQLException("No stock item found for row: " + _stock);
			    	}
				return cursor;
			}
		}
		else
		{	
			return null;
		}  
	}
	/*
	 * Test that the database is opened
	 */
	public void opened() {
		if((stocklistdb == null) || !stocklistdb.isOpen()) {
			stocklistdb = dbHelper.getWritableDatabase();
		}
	}
	
	private boolean isNum(String stock)
	{
		Pattern pattern = Pattern.compile("\\d{6}");
		Matcher isNum = pattern.matcher(stock);
		if( !isNum.matches() )
		{
			return false;
		}
		return true;
	}
	  
	private boolean isPinYin(String stock)
	{
		Pattern pattern = Pattern.compile("\\w+\\w?\\w?\\w?");
		Matcher isPinYin = pattern.matcher(stock);
		if( !isPinYin.matches() )
		{
			return false;
		}
		return true;
	}
	  
	private boolean isChinese(String stock)
	{
		for(int j = 0; j < stock.length(); j++)
		{
			char word = stock.charAt(j);
			String [] pinyinArray = PinyinHelper.toHanyuPinyinStringArray(word);
			if(pinyinArray != null)
			{
				return true;
			}
		}
		return false;
	}
	
	private static class toDoDBOpenHelper extends SQLiteOpenHelper {

		public toDoDBOpenHelper(Context context, String name,
		                        CursorFactory factory, int version) {
			super(context, name, factory, version);
		}

		// SQL Statement to create a new database.
		private static final String DATABASE_CREATE = "create table " + 
			STOCKLISTDB_TABLE + " (" + KEY_ID + " integer primary key autoincrement, " +
		    KEY_STOCK_SEARCH + " text not null, " + KEY_STOCK_NAME + " text not null, " + KEY_STOCK_CODE + " text not null," 
		    + KEY_STOCK_PINYIN + " text not null);";

		@Override
		public void onCreate(SQLiteDatabase _db) {
			_db.execSQL(DATABASE_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) {
			Log.v("TaskDBAdapter", "Upgrading from version " + 
		                           _oldVersion + " to " +
		                           _newVersion + ", which will destroy all old data");

		    // Drop the old table.
		    _db.execSQL("DROP TABLE IF EXISTS " + STOCKLISTDB_TABLE);
		    // Create a new one.
		    onCreate(_db);
		}
	}
}
